﻿-- Copyright 2007-2010 Comdiv (F. Sadykov) - http://code.google.com/u/fagim.sadykov/
-- Supported by Media Technology LTD 
--  
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--  
--      http://www.apache.org/licenses/LICENSE-2.0
--  
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- 
-- MODIFICATIONS HAVE BEEN MADE TO THIS FILE
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---                      COMDIV MacroTSQL v2.0		
---						 CORE INTERFACE (our best practices) MODULE
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------

/*@
<require>comdiv.mtsql.core</require>
@*/
go
/*@
<name>comdiv_table_interface.code</name>
<module name="comdiv.mtsql.core.interfaces" class="i_code" method="_declare" />
<field name='code' type='nvarchar(255)' constraint='not null unique default cast(newid() as nvarchar(255))' />
<method name="id"  isconstraint='1'>
		create function __ (@code nvarchar(255)) returns bigint as begin
			return (select top 1 id from THIS where code = @code )
		end
</method>
<method name="code"  isconstraint='1'>
		create function __ (@id bigint) returns nvarchar(255) as begin
			return (select top 1 code from THIS where id = @id )
		end
</method>
@*/
create proc _ as begin return end
go
/*@
<name>comdiv_table_interface.name</name>
<module name="comdiv.mtsql.core.interfaces" class="i_name" method="_declare" />
<field name='name' type='nvarchar(400)' constraint="not null default '''' " />
@*/create proc _ as begin return end
go

/*@
<name>comdiv_table_interface.uid</name>
<module name="comdiv.mtsql.core.interfaces" class="i_uid" method="_declare" />
<field name='uid' type='uniqueidentifier' constraint="not null default newid()" />
@*/create proc _ as begin return end
go
/*@
<name>comdiv_table_interface.comment</name>
<module name="comdiv.mtsql.core.interfaces" class="i_comment" method="_declare" />
<field name='comment' type='nvarchar(max)' constraint="SPARSE" />
@*/create proc _ as begin return end
go
/*@
<name>comdiv_table_interface.idx</name>
<module name="comdiv.mtsql.core.interfaces" class="i_idx" method="_declare" />
<field name='idx' type='int' constraint="not null default 0" />
@*/create proc _ as begin return end
go
/*@
<name>comdiv_table_interface.hist</name>
<module name="comdiv.mtsql.core.interfaces" class="i_hist" method="_declare" />
<field name='histtime' type='datetime' constraint='null' />
@*/create proc _ as begin return end
go




/*@
<name>comdiv_table_interface.version</name>
<module name="comdiv.mtsql.core.interfaces" class="i_version" method="_declare" />
<field name='version' type='datetime' constraint="not null default (getdate())" />
<method name="edited_ontime"  isconstraint='1'>
		create procedure __ @from datetime = null, @to datetime = null  as begin
			set @from = isnull(@from, cast(0 as datetime))
			set @to = isnull(@to,getdate())
			select * from THIS where version between @from and @to
		end
</method>
<_method name="version_update"  isconstraint='1'>
		create trigger  __ on THIS for update as begin 
			set nocount on
			--updates versions if they are not setted directly (to match both NHibernate and direct SQL activity)
			update THIS
				set version = getdate()
			from deleted d
				where d.version >= THIS.version
				and d.id = THIS.id
		end
</_method>
@*/create proc _ as begin return end
go


/*@
<name>comdiv_table_interface.tree</name>
<module name="comdiv.mtsql.core.interfaces" class="i_tree" method="_declare" />
<field name='parent' type='int' constraint="null constraint fk_NSTABLE_parent foreign key  references THIS(id)" />
<field name='path' type='nvarchar(400)' constraint="not null default ''''" />
<method name="evalpath" isconstraint='1' >
		create function __ (@id int) returns nvarchar(400) as begin
			if @id is null return ''
			declare @c nvarchar(255), @p int
			select @c=code,@p=parent from THIS where id = @id
			set @c = '/'+@c+'/'
			set @c = THIS_evalpath(@p) + @c
			set @c = replace(@c,'//','/')
			return @c
		end
</method>
<method name="path_update" isconstraint='1'>
		create trigger  __ on THIS for update as begin 
			set nocount on
			create table #t (id int)
			insert #t select i.id from inserted i join deleted d on i.id = d.id and isnull(i.parent ,0) != isnull(d.parent,0)
			insert #t select i.id from inserted i join deleted d on i.id = d.id and i.code != d.code
			declare @rc int set @rc = 1
			while @rc > 0 begin
				insert #t select THIS.id from THIS join #t on #t.id = THIS.parent except select id from #t
				set @rc = ROWCOUNT_BIG()
			end
			update THIS set path = THIS_evalpath(id) where id in (select id from #t)
		end
</method>
<method name="path_insert" isconstraint='1'>
		create trigger  __ on THIS for insert as begin 
			set nocount on
			update THIS set path = THIS_evalpath(id) where id in (select id from inserted)
		end
</method>
@*/create proc _ as begin return end
go


/*@
<name>comdiv.get_tag_value</name>
-- static method
<module name="comdiv.mtsql.core.interfaces" class="i_tag" method="getvalue" />
<description>extracts tag value from string contained tags in form " /tagname:tagvalue/ [...]"</description>
@*/
-- provided as utility funciton for 'tag' interface
create function _ (@str nvarchar(max),@tag nvarchar(255)) returns nvarchar(max) as begin
	declare @idx int
	declare @endidx int
	set @idx = charindex('/'+@tag+':',@str,0) --start of tag finded
	if @idx <= 0 return ''
	set @idx = charindex(':',@str,@idx)+1 -- find : symbol which is start of value
	set @endidx = charindex('/',@str,@idx) --find / symbol which is end of value
	return substring(@str,@idx,@endidx - @idx)
end
go



/*@
<name>comdiv.has_tag</name>
-- static method
<module name="comdiv.mtsql.core.interfaces" class="i_tag" method="hastag" />
<description>1 if given tag exists</description>
@*/
-- provided as utility funciton for 'tag' interface
create function _ (@str nvarchar(max),@tag nvarchar(255)) returns bit as begin
	declare @idx int
	declare @endidx int
	set @idx = charindex('/'+@tag+':',@str,0) --start of tag finded
	if @idx <= 0 return 0
	return 1
end
go
/*@
<name>comdiv.set_tag</name>
--static method
<module name="comdiv.mtsql.core.interfaces" class="i_tag" method="setvalue" />
<description>setup value of given tag</description>
@*/
create function _ (@str nvarchar(max), @tagname nvarchar(255), @tagvalue nvarchar(255)) returns nvarchar(max) as begin
	declare @result nvarchar (max) set @result = @str
	declare @i1 int, @i2 int
	if(@tagvalue is null) begin -- удаление
		if comdiv.has_tag(@str, @tagname)=1 begin
			set @i1 = charindex('/'+@tagname+':', @str, 0)
			set @i2 = charindex('/',@str, @i1+1)
			set @result = left(@result,@i1-1) + right(@result, len(@result)-@i2)
		end
	end else begin -- вставка/обновление
		if comdiv.has_tag(@str, @tagname)=1 set @result = comdiv.set_tag(@result, @tagname, null)
		set @result = @result + ' /'+ @tagname+':'+@tagvalue+'/'
	end
	return ltrim(rtrim(@result))
end

go



/*@
<name>comdiv.has_mark</name>
-- static method
<module name="comdiv.mtsql.core.interfaces" class="i_mark" method="getvalue" />
<description>check mark/group list on existene of some string</description>
@*/
-- provided as utility funciton for 'tag' interface
create function _ (@str nvarchar(max),@mark nvarchar(255)) returns bit as begin
	declare @result bit set @result = 0
	if @str like '%/'+@mark+'/%' set @result = 1
	return @result
end
go


/*@
<name>comdiv.unset_mark</name>
-- static method
<module name="comdiv.mtsql.core.interfaces" class="i_mark" method="remove" />
<description>drops mark in list</description>
@*/
-- provided as utility funciton for 'tag' interface
create function _ (@str nvarchar(max),@mark nvarchar(255)) returns nvarchar(max) as begin
	return replace(@str, '/'+@mark+'/','/')
end
go


/*@
<name>comdiv.set_mark</name>
-- static method
<module name="comdiv.mtsql.core.interfaces" class="i_mark" method="set" />
<description>drops mark in list</description>
@*/
-- provided as utility funciton for 'tag' interface
create function _ (@str nvarchar(max),@mark nvarchar(255)) returns nvarchar(max) as begin
	return replace( comdiv.unset_mark(@str,@mark) + '/'+@mark+'/' , '//', '/')
end
go


/*@
<name>comdiv.has_mark</name>
-- static method
<module name="comdiv.mtsql.core.interfaces" class="i_mark" method="getvalue" />
<description>check mark/group list on existene of some string</description>
@*/
-- provided as utility funciton for 'tag' interface
create function _ (@str nvarchar(max),@mark nvarchar(255)) returns bit as begin
	declare @result bit set @result = 0
	if @str like '%/'+@mark+'/%' set @result = 1
	return @result
end
go

/*@
<name>comdiv_table_interface.tag</name>
<module name="comdiv.mtsql.core.interfaces" class="i_tag" method="_declare" />
<field name='tag' type='nvarchar(800)' constraint="SPARSE" />
<method name="by_tag"  isconstraint='1'>
		create procedure __ @tag nvarchar(255), @filter int = 0 as begin
			set @filter = isnull(@filter,0)
			if 0 = @filter begin
				select *,comdiv.get_tag_value(tag,@tag) as tagvalue  from THIS 
					where tag like '%/'+@tag+':%'
			end
			if 1 = @filter begin
				delete #resulttable where NOT (
					tag like '%/'+@tag+':%'
				)
			end
		end
</method>
<_____showsqloncreate/>
@*/create proc _ as begin return end
go
/*@
<name>comdiv_table_interface.usr</name>
<module name="comdiv.mtsql.core.interfaces" class="i_usr" method="_declare" />
<field name='usr' type='nvarchar(255)' constraint="not null default (current_user)" />
<method name='edited_by'  isconstraint='1' >
		create procedure __ @usr nvarchar(255), @filter bit = 0 as begin
			set @filter = isnull(@filter,0)
			if 0 = @filter begin
				select * from THIS where usr like @usr
			end
			if 1 = @filter begin
				delete #resulttable where NOT (usr like @usr)
			end
		end
</method>
@*/create proc _ as begin return end
go
/*@
<name>comdiv_table_interface.entity</name>
<module name="comdiv.mtsql.core.interfaces" class="i_entity" method="_declare" />
<import>code</import>
<import>name</import>
<import>comment</import>
<import>idx</import>
<import>tag</import>
<import>version</import>
<import>usr</import>
<import>uid</import>
<method name='_ent_code_idx' isconstraint='1'>
	if (select object_id from sys.indexes where name = 'NSTHIS_ent_code_idx') is not null
		drop index NSTHIS_ent_code_idx on THIS
	create unique index NSTHIS_ent_code_idx on THIS (code) include (name,comment,idx, tag, version, usr)
</method>
@*/create proc _ as begin return end
go
/*@
<name>comdiv_table_interface.simple</name>
<module name="comdiv.mtsql.core.interfaces" class="i_simple" method="_declare" />
<import>code</import>
<import>tag</import>
<import>version</import>
<import>usr</import>
<import>uid</import>
<method name='_ent_code_idx' isconstraint='1'>
	if (select object_id from sys.indexes where name = 'NSTHIS_ent_code_idx') is not null
		drop index NSTHIS_ent_code_idx on THIS
	create unique index NSTHIS_simple_code_idx on THIS (code) include ( tag, version, usr)
</method>
@*/create proc _ as begin return end
go
/* block of schema testing */
declare @valid int
exec @valid = comdiv.validateschema
if @valid != 100
	select * from comdiv.schemaview where module = 'comdiv.mtsql.core.interfaces'
go